{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["L1 - Text","L2 - Logical","L3 - VLookup","L3 - HLookup","L4 - Database","L4 - Macro & fx","L5 - VBA Demo","Raw"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":3,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"L1 - Text","index":0,"n_rows":57,"n_cols":27,"rows_kept":57,"cut":false,"hidden":false,"merged":[[0,0,0,1],[1,3,1,8],[40,0,40,1]]}
["TEXT Functions"]
[null,null,null,"For sale: baby shoes. Never worn.     "]
[]
[null,null,null,"Result",null,"Usage"]
[null,"LEN",null,null,null,"The Length of String"]
[]
[null,"LEFT",null,null,null,"The Left-most 3 Chars"]
[]
[null,"MID",null,null,null,"Middle 3 Chars, starting at 5th one"]
[]
[null,"RIGHT",null,null,null,"The right-most 10 Chars;  see odd result here, because there is spacesss at the end of sentence."]
[]
[null,"TRIM",null,null,null,"Cut any space before and after the string"]
[]
[null,"LOWER",null,null,null,"Convert all letters to lower case"]
[]
[null,"UPPER",null,null,null,"Convert all letters to UPPER CASE"]
[]
[null,"PROPER",null,null,null,"Convert Words To Proper Case"]
[]
[null,null,"First name","Last name"]
[null,"John Keats"]
[null,"William Yeats"]
[null,"William Blake"]
[null,"Oscar Wilde"]
[null,"John Milton"]
[null,"George Byron"]
[null,"Sylvia Plath"]
[]
[null,"CONCATENATE"]
[]
[]
[null,"SUBSTITUTE",{"$f":"SUBSTITUTE(D19, \"Shoes\", \"Clothes\")","v":""},null,null,"Substitude \"Shoes\" with \"Clothes\""]
[]
[]
[null,"SEARCH",null,null,null,"SEARCH any char in a text , e.g. \"baby\".  Case-insensitively. i.e. the term is found at the 11th character."]
[null,"FIND",null,null,null,"FIND any char in a text , e.g. \"BABY\". Case-sensitively. i.e. no valid result is returned"]
[null,null,null,null,null,"FIND any char in a text , e.g. \"baby\". Case-sensitively. i.e. the term is found at the 11th character."]
[]
[]
["*CHALLENGE"]
["i.","Count specific character -",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Answer:"]
[null,"How many full-stop used in cell[D2]?",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"LEN(D2)-LEN(SUBSTITUTE(D2,\".\",\"\"))","v":2}]
[]
[]
["ii.","more on PROPER() function -",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Answer:"]
[null,"Boat people (1982)",null,{"$f":"PROPER(B47)","v":"Boat People (1982)"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(findUcase(B47),B47,SUBSTITUTE(PROPER(B47),\"'S\",\"'s\"))","v":"Boat People (1982)"}]
[null,"The shindler's list (1993)",null,{"$f":"PROPER(B48)","v":"The Shindler'S List (1993)"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(findUcase(B48),B48,SUBSTITUTE(PROPER(B48),\"'S\",\"'s\"))","v":"The Shindler's List (1993)"}]
[null,"JFK (2003)",null,"Jfk (2003)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(findUcase(B49),B49,SUBSTITUTE(PROPER(B49),\"'S\",\"'s\"))","v":"JFK (2003)"}]
[null,null,null,"Try to modify them with if() and custom function?",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(findUcase(B50),B50,SUBSTITUTE(PROPER(B50),\"'S\",\"'s\"))","v":""}]
[]
[null,null,null,null,null,null,null,null,"Hint #1: spot out any 's and replace the odd outcome after the PROPER() output."]
[]
[null,null,null,null,null,null,null,null,"Hint #2: custom function, findUcase(), is prepared in this workbook"]
[null,null,null,null,null,null,null,null,"PTU",null,{"$f":"findUcase(I55)","v":true}]
[null,null,null,null,null,null,null,null,"E.T. the Extra-Terrestrial",null,{"$f":"findUcase(I56)","v":true}]
[null,null,null,null,null,null,null,null,"The Great Passage",null,{"$f":"findUcase(I57)","v":false}]

{"sheet":"L2 - Logical","index":1,"n_rows":50,"n_cols":11,"rows_kept":50,"cut":false,"hidden":false,"merged":[[0,0,0,1],[1,0,1,1],[3,8,3,9]]}
["LOGICAL OPERATORS"]
[">   <   <>   =   >=   <="]
[]
[null,null,null,null,null,null,null,null,"Passing Mark = 50"]
[null,null,"Math","Painting","Music","Astronomy","Language","Average","Passed?","Math & Language = Pass / Fail?"]
[null,"Albert Einstein",99,14,13,66,39]
[null,"Wernher von Braun",12,85,83,73,56]
[null,"Jean-François Champollion",60,75,95,80,74]
[null,"Galileo Galilei",88,66,55,33,33]
[null,"Isaac Newton",77,94,88,73,88]
[null,"Stephen W. Hawking",42,23,44,36,55]
[null,"Ptolemy",55,73,43,95,43]
[null,"J. Robert Oppenheimer",53,58,84,52,55]
[]
[null,"IF(logical_test, [value_if_TRUE], [value_if_FALSE]) "]
[]
[null,null,"AND(logical1, [logical2], ...) ",null,null,"Logical1","Logical2","AND",null,"Data"]
[null,null,null,null,null,null,null,null,null,50]
[null,null,null,null,null,"True","True",true,null,"Question","Result"]
[null,null,null,null,null,"True","False",false,null,"Check if data is  between 1 and 100 ?",{"$f":"AND(J18>=1,J18<=100)","v":true}]
[null,null,null,null,null,"False","True",false,null,"Check if data is between 1 and 49 ?"]
[null,null,null,null,null,"False","False",false]
[]
[null,null,"OR(logical1, [logical2], ...) ",null,null,"Logical1","Logical2","OR",null,"Data"]
[null,null,null,null,null,null,null,null,null,50]
[null,null,null,null,null,"True","True",true,null,"Question","Result"]
[null,null,null,null,null,"True","False",true,null,"less than 60 or greater than 100 ?",{"$f":"OR(J25<60,J25>100)","v":true}]
[null,null,null,null,null,"False","True",true,null,"negative number or greater than 100 ?"]
[null,null,null,null,null,"False","False",false]
[]
[]
[null,null,"Invoice","Month","Sales Person","Product","Sales Value"]
[null,null,"A001","Jan","John","Wooden Chair",4500]
[null,null,"A002","Jan","Steve","Wooden Chair",2300]
[null,null,"A003","Jan","Mary","Metal Chair",3300]
[null,null,"A004","Jan","John","Wooden Chair",3700]
[null,null,"A005","Feb","Danny","Wooden Chair",5400]
[null,null,"A006","Feb","Anson","Metal Chair",3100]
[null,null,"A007","Feb","Mary","Wooden Chair",5200]
[null,null,"A008","Feb","Mary","Table",2800]
[]
[null,"SUMIF",null,null,"John",null,{"$f":"SUMIF(E33:E40,E42,G33:G40)","v":8200}]
[null,"SUMIF",null,"Feb"]
[null,"SUMIFS",null,"Jan",null,"Wooden Chair"]
[]
[null,"COUNTIF",null,null,null,"Metal Chair",{"$f":"COUNTIF(F33:F40,F46)","v":2}]
[null,"COUNTIFS",null,"Jan","Mary"]
[]
[null,"AVERAGEIF",null,null,null,"Wooden Chair"]
[null,"AVERAGEIFS",null,"Feb",null,"*Chair"]

{"sheet":"L3 - VLookup","index":2,"n_rows":51,"n_cols":32,"rows_kept":51,"cut":false,"hidden":false,"merged":[[0,0,0,1],[42,3,42,4],[38,0,38,1]]}
["VERTICAL LOOKUP"]
["VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])"]
[]
[null,"Item","Sales Price","Inventory","Sales","Total Sales ($)","Stock Left"]
[null,"Table",30,100,51,{"$f":"C5*E5","v":1530},{"$f":"D5-E5","v":49}]
[null,"Chair",10,50,20,{"$f":"C6*E6","v":200},{"$f":"D6-E6","v":30}]
[null,"Shelf",20,75,5,100,70]
[null,"Lamp",5,80,76,{"$f":"C8*E8","v":380},4]
[]
[null,"Item","Chair","Total Sales",{"$f":"VLOOKUP(C10,B5:G8,5, FALSE)","v":200}]
[null,null,null,"Stock Left",{"$f":"VLOOKUP(C10,B5:G8, 6, FALSE)","v":30}]
[]
[]
[]
[]
[]
[]
[null,"Mark","Grade System","Student Mark",80]
[null,0,"F"]
[null,40,"E"]
[null,60,"D"]
[null,70,"C"]
[null,80,"B"]
[null,85,"A","Student Grade",{"$f":"VLOOKUP(E18, B19:C24, 2, TRUE)","v":"B"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["*CHALLENGE"]
[null,"To enhance sales figures, price will cut 20% off for any product with >50% stock left."]
[null,"Thus, how much possible Sales Income for the remaining stock?"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Answer:"]
[null,"Item","Shelf","Possible Sales Income ($)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"_xlfn.IFNA(IF(VLOOKUP(C43, B5:G8, 6, FALSE)/VLOOKUP(C43, B5:G8, 3, FALSE)>0.5, VLOOKUP(C43, B5:G8, 6, FALSE)*VLOOKUP(C43, B5:G8, 2, FALSE)*0.8, VLOOKUP(C43, B5:G8, 6, FALSE)*VLOOKUP(C43, B5:G8, 2, FALSE)), \"-\")","v":1120}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Or, you can build a dynamic table,"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"to help solving the complex formula -"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"VLOOKUP(C43,AC48:AF51, 4, FALSE)","v":1120},null,"Item","Stock >50%?","New Price","Possible Income"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Table",{"$f":"IF($G5/$D5>0.5, \"Yes\", \"No\")","v":"No"},{"$f":"IF(AD48=\"Yes\", $C5*0.8, $C5)","v":30},{"$f":"$G5*AE48","v":1470}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Chair",{"$f":"IF($G6/$D6>0.5, \"Yes\", \"No\")","v":"Yes"},{"$f":"IF(AD49=\"Yes\", $C6*0.8, $C6)","v":8},{"$f":"$G6*AE49","v":240}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Shelf","Yes",{"$f":"IF(AD50=\"Yes\", $C7*0.8, $C7)","v":16},1120]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Lamp","No",{"$f":"IF(AD51=\"Yes\", $C8*0.8, $C8)","v":5},20]

{"sheet":"L3 - HLookup","index":3,"n_rows":36,"n_cols":6,"rows_kept":36,"cut":false,"hidden":false,"merged":[[0,0,0,1],[31,2,31,9],[32,2,32,9],[29,0,29,1]]}
["HORIZONTAL LOOKUP"]
["HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])"]
[]
[null,"Items","Chair","Table","Shelf","Lamp"]
[null,"Sales Price",10,20,15,5]
[null,"Inventory",100,50,75,80]
[null,"Sales",45,2,5,76]
[null,"Total Sales",{"$f":"C5*C7","v":450},{"$f":"D5*D7","v":40},{"$f":"E5*E7","v":75},380]
[null,"Stock Left",{"$f":"C6-C7","v":55},{"$f":"D6-D7","v":48},70,4]
[]
[]
[null,null,"Item","Table","Sold",{"$f":"HLOOKUP(D12, B4:F9, 4, FALSE)","v":2}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["FURTHER STUDIES"]
[]
[null,"XLOOKUP\n(since Office 365)","Look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.\n\nXLOOKUP() can look up both vertically and horizontally and perform an exact match (default), approximate (closest) match, or wildcard (partial) match."]
[null,"INDEX / MATCH\n(since Office 2007)","Classic tools for dataset. \n\nVLOOKUP() and HLOOKUP() can be very useful, however, sometimes data that isn’t organised left to right or, with values sorted in descending (rather than ascending), order. \nThe combination of the MATCH() and INDEX() functions can help you do so."]
[]
[null,"Reference site -"]
[null,"https://corporatefinanceinstitute.com/resources/excel/study/xlookup-function-in-excel/"]

{"sheet":"L4 - Database","index":4,"n_rows":24,"n_cols":6,"rows_kept":24,"cut":false,"hidden":false,"merged":[[0,0,0,1]]}
["DATABASE"]
[]
[]
[null,"DSUM(database, field, criteria)"]
[]
[null,"DAVERAGE(database, field, criteria)"]
[]
[null,"DCOUNT(database, field, criteria)"]
[]
[null,"DCOUNTA(database, field, criteria)"]
[]
[null,"DMAX(database, field, criteria)"]
[]
[null,"DMIN(database, field, criteria)"]
[]
[]
[null,"Invoice","Month","Sales Person","Product","Sales Value"]
[null,"A001","Feb","John Smith","Wooden Chair",4500]
[null,"A002","Feb","Steve Brown","Wooden Chair",2300]
[null,"A003","Feb","Mary Davis","Metal Chair",3300]
[null,"A004","Feb","John Smith","Wooden Chair",3700]
[null,"A005","Mar","Mary Davis","Wooden Chair",5400]
[null,"A006","Mar","John Smith","Metal Chair",3100]
[null,"A007","Mar","Mary Davis","Table",5000]

{"sheet":"L4 - Macro & fx","index":5,"n_rows":72,"n_cols":14,"rows_kept":72,"cut":false,"hidden":false,"merged":[[0,0,0,1],[18,0,18,1]]}
["MACRO & UDF",null,null,"invoice","loan_amnt","term","int_rate","installment","grade","emp_length","home_ownership","annual_inc","verification_status","loan_status"]
[null,null,null,1077501,5000," 36 months",10.65,162.87,"B","10+ years","RENT",24000,"Verified","Fully Paid"]
[null,null,null,1077430,2500," 60 months",15.27,59.83,"C","< 1 year","RENT",30000,"Source Verified","Delinquent"]
["MACROs",null,null,1077175,2400," 36 months",15.96,84.33,"C","10+ years","RENT",12252,"Not Verified","Fully Paid"]
[null,null,null,1076863,10000," 36 months",13.49,339.31,"C","10+ years","RENT",49200,"Source Verified","Fully Paid"]
["1. Record your work flow",null,null,1075358,3000," 60 months",12.69,67.790000000000006,"B","1 year","RENT",80000,"Source Verified","Current"]
[null,null,null,1075269,5000," 36 months",7.9,156.46,"A","3 years","RENT",36000,"Source Verified","Fully Paid"]
["2. Examine the Macro in Visual Basic Editor (Alt-F11)",null,null,1069639,7000," 60 months",15.96,170.08,"C","8 years","RENT",47004,"Not Verified","Current"]
[null,null,null,1072053,3000," 36 months",18.64,109.43,"E","9 years","RENT",48000,"Source Verified","Fully Paid"]
["3. Test out and modify",null,null,1071795,5600," 60 months",21.28,152.38999999999999,"F","4 years","OWN",40000,"Source Verified","Delinquent"]
[null,null,null,1071570,5375," 60 months",12.69,121.45,"B","< 1 year","RENT",15000,"Verified","Delinquent"]
[null,null,null,1070078,6500," 60 months",14.65,153.44999999999999,"C","5 years","OWN",72000,"Not Verified","Fully Paid"]
[null,null,null,1069908,12000," 36 months",12.69,402.54,"B","10+ years","OWN",75000,"Source Verified","Fully Paid"]
[null,null,null,1064687,9000," 36 months",13.49,305.38,"C","< 1 year","RENT",30000,"Source Verified","Delinquent"]
["User-defined Functions",null,null,1069866,3000," 36 months",9.91,96.68,"B","3 years","RENT",15000,"Source Verified","Fully Paid"]
[null,null,null,1069057,10000," 36 months",10.65,325.74,"B","3 years","RENT",100000,"Source Verified","Delinquent"]
["1. Customized function, e.g. advanced mathematics and special text.",null,null,1069759,1000," 36 months",16.29,35.31,"D","< 1 year","RENT",28000,"Not Verified","Fully Paid"]
[null,null,null,1065775,10000," 36 months",15.27,347.98,"C","4 years","RENT",42000,"Not Verified","Fully Paid"]
["2. Useful for content manipulation or date calculations prior to 1900.",null,null,1069971,3600," 36 months",6.03,109.57,"A","10+ years","MORTGAGE",110000,"Not Verified","Fully Paid"]
[null,null,null,1062474,6000," 36 months",11.71,198.46,"B","1 year","MORTGAGE",84000,"Verified","Fully Paid"]
["3. Workbook with UDF will also trigger the \"macro\" flag (Macros Security)",null,null,1069742,9200," 36 months",6.03,280.01,"A","6 years","RENT",77385.19,"Not Verified","Fully Paid"]
[null,null,null,1069740,20250," 60 months",15.27,484.63,"C","3 years","RENT",43370,"Verified","Fully Paid"]
[null,null,null,1039153,21000," 36 months",12.42,701.73,"B","10+ years","RENT",105000,"Verified","Delinquent"]
[null,null,null,1069710,10000," 36 months",11.71,330.76,"B","10+ years","OWN",50000,"Source Verified","Fully Paid"]
[null,null,null,1069700,10000," 36 months",11.71,330.76,"B","5 years","RENT",50000,"Not Verified","Fully Paid"]
[null,null,null,1069559,6000," 36 months",11.71,198.46,"B","1 year","RENT",76000,"Not Verified","Delinquent"]
[null,null,null,1069697,15000," 36 months",9.91,483.38,"B","2 years","MORTGAGE",92000,"Verified","Fully Paid"]
[null,null,null,1069800,15000," 36 months",14.27,514.64,"C","9 years","RENT",60000,"Not Verified","Delinquent"]
[null,null,null,1069657,5000," 60 months",16.77,123.65,"D","2 years","RENT",50004,"Not Verified","Delinquent"]
[null,null,null,1069799,4000," 36 months",11.71,132.31,"B","10+ years","MORTGAGE",106000,"Not Verified","Fully Paid"]
[null,null,null,1047704,8500," 36 months",11.71,281.14999999999998,"B","< 1 year","RENT",25000,"Source Verified","Fully Paid"]
[null,null,null,1032111,4375," 36 months",7.51,136.11000000000001,"A","7 years","MORTGAGE",17108,"Source Verified","Fully Paid"]
[null,null,null,1069539,31825," 36 months",7.9,995.82,"A","5 years","MORTGAGE",75000,"Verified","Fully Paid"]
[null,null,null,1065420,10000," 60 months",15.96,242.97,"C","2 years","RENT",29120,"Verified","Current"]
[null,null,null,1069591,5000," 36 months",8.9,158.77000000000001,"A","2 years","RENT",24044,"Verified","Fully Paid"]
[null,null,null,1069530,7000," 36 months",15.96,245.97,"C","7 years","RENT",34000,"Source Verified","Fully Paid"]
[null,null,null,1069522,12400," 36 months",10.65,403.91,"B","10+ years","RENT",41000,"Not Verified","Fully Paid"]
[null,null,null,1069361,10800," 36 months",9.91,348.03,"B","2 years","RENT",55596,"Source Verified","Fully Paid"]
[null,null,null,1069357,15000," 36 months",7.9,469.36,"A","1 year","RENT",45000,"Verified","Fully Paid"]
[null,null,null,1069356,6000," 36 months",12.42,200.5,"B","1 year","RENT",36852,"Source Verified","Fully Paid"]
[null,null,null,1069346,12500," 60 months",12.69,282.44,"B","1 year","RENT",27000,"Verified","Current"]
[null,null,null,1067573,9600," 36 months",7.51,298.67,"A","4 years","RENT",68004,"Source Verified","Fully Paid"]
[null,null,null,1069506,12000," 36 months",7.9,375.49,"A","10+ years","RENT",62300,"Not Verified","Fully Paid"]
[null,null,null,1069314,3000," 36 months",18.25,108.84,"D","9 years","MORTGAGE",65000,"Not Verified","Fully Paid"]
[null,null,null,1060578,4400," 36 months",16.77,156.37,"D","10+ years","RENT",55000,"Not Verified","Fully Paid"]
[null,null,null,1069469,6000," 36 months",6.03,182.62,"A","10+ years","MORTGAGE",45600,"Not Verified","Fully Paid"]
[null,null,null,1051117,14000," 36 months",9.91,451.15,"B","6 years","RENT",80000,"Not Verified","Fully Paid"]
[null,null,null,1069465,5000," 36 months",8.9,158.77000000000001,"A","10+ years","MORTGAGE",100000,"Source Verified","Delinquent"]
[null,null,null,1069283,10000," 36 months",10.65,325.74,"B","6 years","RENT",27000,"Verified","Fully Paid"]
[null,null,null,1069287,10000," 36 months",6.03,304.36,"A","10+ years","RENT",60000,"Not Verified","Fully Paid"]
[null,null,null,1069453,11000," 36 months",6.62,337.75,"A","5 years","RENT",70000,"Not Verified","Fully Paid"]
[null,null,null,1069248,15000," 36 months",9.91,483.38,"B","8 years","MORTGAGE",80000,"Not Verified","Delinquent"]
[null,null,null,1068120,25600," 36 months",9.91,824.96,"B","9 years","RENT",110000,"Verified","Fully Paid"]
[null,null,null,1069244,7000," 36 months",10.65,228.02,"B","< 1 year","RENT",40000,"Not Verified","Fully Paid"]
[]
[]
[]
[]
[]
[]
["REFERENCE"]
["Microsoft Excel Official Support"]
["support.microsoft.com/en-us/excel"]
[]
["Microsoft Knowledge Base"]
["- Excel Fomulas Overview"]
["- Pivot Tables"]
["- Getting started with VBA in Office"]
[]
["External Tutorials"]
[" - XLOOKUP, powerful next-gen & combined VLookup & HLookup"]
[" - LAMBDA, set custom functions without macro / code"]

{"sheet":"L5 - VBA Demo","index":6,"n_rows":67,"n_cols":39,"rows_kept":67,"cut":false,"hidden":false,"merged":[]}
[]
["á é í ó ú",null,null,"Visual Basic Automation (VBA)"]
["Á É Í Ó Ú"]
["ä ö Ü Ö ë",null,null,"1. Modify Macro / Codes with Visual Basic for Applications"]
["à è Ù Ò"]
["ã ñ Ñ Õ",null,null,"2. Work with more structured Variables, Loops, and validations."]
["â ô Ê Î"]
["Reference Sources - ",null,null,"3. Communicate with other Microsoft softwares, e.g. Word, Powerpoint, Project, Visio..."]
["1. 《南洋商報》"]
["2. 《讀賣新聞》"]
["3. Aamulehti"]
["4. Auckland Star"]
["5. Columbia Missourian Newspaper"]
["6. El Paso Herald"]
["7. Evening Post"]
["8. Excelsior"]
["9. Helsingin Sanomat"]
["10. Honolulu Star-Advertiser"]
["11. Illustrated London News"]
["12. Illustrierte Wochenpost"]
["13. La Presse"]
["14. Lahaina News"]
["15. Le Journal"]
["16. Le Progrès de la Côte-d'Or"]
["17. Les Annales politiques et littéraires"]
["18. Lloyd's Weekly Newspaper"]
["19. L'Ouest-Éclair"]
["20. New Zealand Time"]
["21. Otago Witness"]
["22. The Chinese times"]
["23. The Graphic"]
["24. The Star Online"]
["25. The Times of India"]
["26. The World News"]
["27. Times Colonist"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["*CHALLENGE"]
["Re-write the following data into output format by applying VBA (or Formulas) -",null,"output",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Answer: (A single VBA / Macro Formula is possible, but too complicated for demo purpose, here is the break down from left to right.)"]
["Name of Book & Author","Publishing Year","Desired Format",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"RIGHT ('Author Name') after comma","->","Apply f(x) in Hint #1","->","Apply Formula in Hint #2","->","Right (Family Name)","Left (Initials)","->","Fix Year","Left('Book Title') before comma","->","Concatenate all elements = TASK DONE!"]
["In Search of Lost Time, Marcel Proust",1913,"Proust, M. (1913). In Search of Lost Time.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"RIGHT(A48, LEN(A48) - FIND(\",\",A48,1) -1)","v":"Marcel Proust"},null,{"$f":"initialfirstname(AA48)","v":"M. Proust"},null,{"$f":"FIND(\"@\",SUBSTITUTE(AC48,\".\",\"@\",LEN(AC48)-LEN(SUBSTITUTE(AC48,\".\",\"\"))))","v":2},null,{"$f":"RIGHT(AC48, LEN(AC48)-AE48 -1) & \", \"","v":"Proust, "},{"$f":"LEFT(AC48, AE48) & \" \"","v":"M. "},null,{"$f":"CONCATENATE(\"(\", B48, \")\") & \".  \"","v":"(1913).  "},{"$f":"LEFT(A48,FIND(\",\",A48)-1) & \".\"","v":"In Search of Lost Time."},null,{"$f":"CONCATENATE(AG48, AH48, AJ48,AK48)","v":"Proust, M. (1913).  In Search of Lost Time."}]
["Ulysses, James Joyce",1920,"Joyce, J. (1920). Ulysses.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"RIGHT(A49, LEN(A49) - FIND(\",\",A49,1) -1)","v":"James Joyce"},null,{"$f":"initialfirstname(AA49)","v":"J. Joyce"},null,{"$f":"FIND(\"@\",SUBSTITUTE(AC49,\".\",\"@\",LEN(AC49)-LEN(SUBSTITUTE(AC49,\".\",\"\"))))","v":2},null,{"$f":"RIGHT(AC49, LEN(AC49)-AE49 -1) & \", \"","v":"Joyce, "},{"$f":"LEFT(AC49, AE49) & \" \"","v":"J. "},null,{"$f":"CONCATENATE(\"(\", B49, \")\") & \".  \"","v":"(1920).  "},{"$f":"LEFT(A49,FIND(\",\",A49)-1) & \".\"","v":"Ulysses."},null,{"$f":"CONCATENATE(AG49, AH49, AJ49,AK49)","v":"Joyce, J. (1920).  Ulysses."}]
["Don Quixote, Miguel de Cervantes",1612,"de Cervantes, M. (1612). Don Quixote.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Miguel de Cervantes",null,{"$f":"initialfirstname(AA50)","v":"M. de Cervantes"},null,2,null,"de Cervantes, ","M. ",null,"(1612).  ","Don Quixote.",null,"de Cervantes, M. (1612).  Don Quixote."]
["One Hundred Years of Solitude, Gabriel Garcia Marquez",1967,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Gabriel Garcia Marquez",null,{"$f":"initialfirstname(AA51)","v":"G. G. Marquez"},null,5,null,"Marquez, ","G. G. ",null,"(1967).  ","One Hundred Years of Solitude.",null,"Marquez, G. G. (1967).  One Hundred Years of Solitude."]
["The Great Gatsby, F. Scott Fitzgerald",1925,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"F. Scott Fitzgerald",null,{"$f":"initialfirstname(AA52)","v":"F. S. Fitzgerald"},null,5,null,"Fitzgerald, ","F. S. ",null,"(1925).  ","The Great Gatsby.",null,"Fitzgerald, F. S. (1925).  The Great Gatsby."]
["War and Peace, Leo Tolstoy",1867,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Leo Tolstoy",null,{"$f":"initialfirstname(AA53)","v":"L. Tolstoy"},null,2,null,"Tolstoy, ","L. ",null,"(1867).  ","War and Peace.",null,"Tolstoy, L. (1867).  War and Peace."]
["Hamlet, William Shakespeare",1603,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"William Shakespeare",null,{"$f":"initialfirstname(AA54)","v":"W. Shakespeare"},null,2,null,"Shakespeare, ","W. ",null,"(1603).  ","Hamlet.",null,"Shakespeare, W. (1603).  Hamlet."]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"* if we neglect the particles e.g. 'de', 'von', 'van'…"]
["Hint #1 - A custom function, initialfirstname(), is tailored made in this workbook -",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"we can simply apply formula in Hint #3:"]
["John Doe",{"$f":"initialfirstname(A58)","v":"J. Doe"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"RIGHT(AA50,LEN(AA50)-FIND(\"@\",SUBSTITUTE(AA50,\" \",\"@\",LEN(AA50)-LEN(SUBSTITUTE(AA50,\" \",\"\"))),1)) & \", \"","v":"Cervantes, "},{"$f":"LEFT(AC50, AE50) & \" \"","v":"M. "},null,"…","…",null,{"$f":"CONCATENATE(AG58, AH50, AJ50,AK50)","v":"Cervantes, M. (1612).  Don Quixote."}]
["Wolfgang Amadeus Mozart",{"$f":"initialfirstname(A59)","v":"W. A. Mozart"}]
[]
["Hint #2 - Useful fomula: Find out where in the text is the last full-stop -"]
["J. Doe",{"$f":"FIND(\"@\",SUBSTITUTE(A62,\".\",\"@\",LEN(A62)-LEN(SUBSTITUTE(A62,\".\",\"\"))))","v":2}]
["W. A. Mozart",{"$f":"FIND(\"@\",SUBSTITUTE(A63,\".\",\"@\",LEN(A63)-LEN(SUBSTITUTE(A63,\".\",\"\"))))","v":5}]
[]
["Hint #3 - Useful fomula: Find the Last Word -"]
["John Doe",{"$f":"RIGHT(A66,LEN(A66)-FIND(\"@\",SUBSTITUTE(A66,\" \",\"@\",LEN(A66)-LEN(SUBSTITUTE(A66,\" \",\"\"))),1))","v":"Doe"}]
["e.g. Wolfgang Amadeus Mozart + something",{"$f":"RIGHT(A67,LEN(A67)-FIND(\"@\",SUBSTITUTE(A67,\" \",\"@\",LEN(A67)-LEN(SUBSTITUTE(A67,\" \",\"\"))),1))","v":"something"}]

{"sheet":"Raw","index":7,"n_rows":54,"n_cols":11,"rows_kept":54,"cut":false,"hidden":false,"merged":[]}
["invoice","loan_amnt","term","int_rate","installment","grade","emp_length","home_ownership","annual_inc","verification_status","loan_status"]
[1069697,15000," 36 months",9.91,483.38,"B","2 years","MORTGAGE",92000,"Verified","Fully Paid"]
[1069800,15000," 36 months",14.27,514.64,"C","9 years","RENT",60000,"Not Verified","Delinquent"]
[1069657,5000," 60 months",16.77,123.65,"D","2 years","RENT",50004,"Not Verified","Delinquent"]
[1069799,4000," 36 months",11.71,132.31,"B","10+ years","MORTGAGE",106000,"Not Verified","Fully Paid"]
[1047704,8500," 36 months",11.71,281.14999999999998,"B","< 1 year","RENT",25000,"Source Verified","Fully Paid"]
[1032111,4375," 36 months",7.51,136.11000000000001,"A","7 years","MORTGAGE",17108,"Source Verified","Fully Paid"]
[1069539,31825," 36 months",7.9,995.82,"A","5 years","MORTGAGE",75000,"Verified","Fully Paid"]
[1065420,10000," 60 months",15.96,242.97,"C","2 years","RENT",29120,"Verified","Current"]
[1069591,5000," 36 months",8.9,158.77000000000001,"A","2 years","RENT",24044,"Verified","Fully Paid"]
[1069530,7000," 36 months",15.96,245.97,"C","7 years","RENT",34000,"Source Verified","Fully Paid"]
[1069522,12400," 36 months",10.65,403.91,"B","10+ years","RENT",41000,"Not Verified","Fully Paid"]
[1069361,10800," 36 months",9.91,348.03,"B","2 years","RENT",55596,"Source Verified","Fully Paid"]
[1069357,15000," 36 months",7.9,469.36,"A","1 year","RENT",45000,"Verified","Fully Paid"]
[1069356,6000," 36 months",12.42,200.5,"B","1 year","RENT",36852,"Source Verified","Fully Paid"]
[1069346,12500," 60 months",12.69,282.44,"B","1 year","RENT",27000,"Verified","Current"]
[1067573,9600," 36 months",7.51,298.67,"A","4 years","RENT",68004,"Source Verified","Fully Paid"]
[1069506,12000," 36 months",7.9,375.49,"A","10+ years","RENT",62300,"Not Verified","Fully Paid"]
[1069314,3000," 36 months",18.25,108.84,"D","9 years","MORTGAGE",65000,"Not Verified","Fully Paid"]
[1060578,4400," 36 months",16.77,156.37,"D","10+ years","RENT",55000,"Not Verified","Fully Paid"]
[1069469,6000," 36 months",6.03,182.62,"A","10+ years","MORTGAGE",45600,"Not Verified","Fully Paid"]
[1051117,14000," 36 months",9.91,451.15,"B","6 years","RENT",80000,"Not Verified","Fully Paid"]
[1069465,5000," 36 months",8.9,158.77000000000001,"A","10+ years","MORTGAGE",100000,"Source Verified","Delinquent"]
[1069283,10000," 36 months",10.65,325.74,"B","6 years","RENT",27000,"Verified","Fully Paid"]
[1069287,10000," 36 months",6.03,304.36,"A","10+ years","RENT",60000,"Not Verified","Fully Paid"]
[1069453,11000," 36 months",6.62,337.75,"A","5 years","RENT",70000,"Not Verified","Fully Paid"]
[1069248,15000," 36 months",9.91,483.38,"B","8 years","MORTGAGE",80000,"Not Verified","Delinquent"]
[1068120,25600," 36 months",9.91,824.96,"B","9 years","RENT",110000,"Verified","Fully Paid"]
[1069244,7000," 36 months",10.65,228.02,"B","< 1 year","RENT",40000,"Not Verified","Fully Paid"]
[1077501,5000," 36 months",10.65,162.87,"B","10+ years","RENT",24000,"Verified","Fully Paid"]
[1077430,2500," 60 months",15.27,59.83,"C","< 1 year","RENT",30000,"Source Verified","Delinquent"]
[1077175,2400," 36 months",15.96,84.33,"C","10+ years","RENT",12252,"Not Verified","Fully Paid"]
[1076863,10000," 36 months",13.49,339.31,"C","10+ years","RENT",49200,"Source Verified","Fully Paid"]
[1075358,3000," 60 months",12.69,67.790000000000006,"B","1 year","RENT",80000,"Source Verified","Current"]
[1075269,5000," 36 months",7.9,156.46,"A","3 years","RENT",36000,"Source Verified","Fully Paid"]
[1069639,7000," 60 months",15.96,170.08,"C","8 years","RENT",47004,"Not Verified","Current"]
[1072053,3000," 36 months",18.64,109.43,"E","9 years","RENT",48000,"Source Verified","Fully Paid"]
[1071795,5600," 60 months",21.28,152.38999999999999,"F","4 years","OWN",40000,"Source Verified","Delinquent"]
[1071570,5375," 60 months",12.69,121.45,"B","< 1 year","RENT",15000,"Verified","Delinquent"]
[1070078,6500," 60 months",14.65,153.44999999999999,"C","5 years","OWN",72000,"Not Verified","Fully Paid"]
[1069908,12000," 36 months",12.69,402.54,"B","10+ years","OWN",75000,"Source Verified","Fully Paid"]
[1064687,9000," 36 months",13.49,305.38,"C","< 1 year","RENT",30000,"Source Verified","Delinquent"]
[1069866,3000," 36 months",9.91,96.68,"B","3 years","RENT",15000,"Source Verified","Fully Paid"]
[1069057,10000," 36 months",10.65,325.74,"B","3 years","RENT",100000,"Source Verified","Delinquent"]
[1069759,1000," 36 months",16.29,35.31,"D","< 1 year","RENT",28000,"Not Verified","Fully Paid"]
[1065775,10000," 36 months",15.27,347.98,"C","4 years","RENT",42000,"Not Verified","Fully Paid"]
[1069971,3600," 36 months",6.03,109.57,"A","10+ years","MORTGAGE",110000,"Not Verified","Fully Paid"]
[1062474,6000," 36 months",11.71,198.46,"B","1 year","MORTGAGE",84000,"Verified","Fully Paid"]
[1069742,9200," 36 months",6.03,280.01,"A","6 years","RENT",77385.19,"Not Verified","Fully Paid"]
[1069740,20250," 60 months",15.27,484.63,"C","3 years","RENT",43370,"Verified","Fully Paid"]
[1039153,21000," 36 months",12.42,701.73,"B","10+ years","RENT",105000,"Verified","Delinquent"]
[1069710,10000," 36 months",11.71,330.76,"B","10+ years","OWN",50000,"Source Verified","Fully Paid"]
[1069700,10000," 36 months",11.71,330.76,"B","5 years","RENT",50000,"Not Verified","Fully Paid"]
[1069559,6000," 36 months",11.71,198.46,"B","1 year","RENT",76000,"Not Verified","Delinquent"]